package com.guowen.myaccount.db;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

/*
    负责管理数据库的类
    主要对于表中的内容进行操作，增删改查
 */
public class DBManager {

    private static SQLiteDatabase db;

    // 初始化数据库对象
    public static void initDB(Context context){
        DBOpenHelper helper = new DBOpenHelper(context);
        db = helper.getReadableDatabase();
    }

    // 获取数据库中menutb对象的数据
    @SuppressLint("Range")
    public static List<MenuBean> getMenuBean(int kind){
        List<MenuBean> list = new ArrayList<>();
        // 读取menutb表中的数据
        String sql = "select * from menutb where kind =" + kind;
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            String menuName = cursor.getString(cursor.getColumnIndex("menuName"));
            int imageId = cursor.getInt(cursor.getColumnIndex("imageId"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            int kind1 = cursor.getInt(cursor.getColumnIndex("kind"));
            MenuBean menuBean = new MenuBean(imageId,sImageId,menuName,kind1);
            list.add(menuBean);
        }
        return list;
    }

    /**
     * 读取数据库当中的数据，写入内存集合里
     * kind：表示收入或者支出
     */
    @SuppressLint("Range")
    public static List<TypeBean> getTypeList(int kind){
        List<TypeBean> list = new ArrayList<>();
        // 读取Typetb表中的数据
        String sql = "select * from typetb where kind = " + kind;
        Cursor cursor = db.rawQuery(sql, null);
        // 循环读取游标内容，存储到对象当中
        while (cursor.moveToNext()) {
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            int imageId = cursor.getInt(cursor.getColumnIndex("imageId"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind1 = cursor.getInt(cursor.getColumnIndex("kind"));
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            TypeBean typeBean = new TypeBean(id, typename, imageId, sImageId, kind1);
            list.add(typeBean);
        }
        return list;
    }

    // 读取数据库中支出或者收入的Item
    @SuppressLint("Range")
    public static List<AccountBean> getAccountList(int kind){
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from accounttb where kind="+kind;
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            Float money = cursor.getFloat(cursor.getColumnIndex("money"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            String account = cursor.getString(cursor.getColumnIndex("account"));
            int year = cursor.getInt(cursor.getColumnIndex("year"));
            int month = cursor.getInt(cursor.getColumnIndex("month"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean bean = new AccountBean(id,sImageId,kind,typename,beizhu,time,account,money,year,month,day);
            list.add(bean);
        }
        return list;
    }

    @SuppressLint("Range")
    public static List<AccManageBean> getAccManList(){
        List<AccManageBean> list = new ArrayList<>();
        //读取accmanagetb中的数据
        String sql = "select * from accmanagetb";
        Cursor cursor = db.rawQuery(sql, null);
        // 循环读取游标内容，存储到对象当中
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int year = cursor.getInt(cursor.getColumnIndex("year"));
            int month = cursor.getInt(cursor.getColumnIndex("month"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccManageBean accManageBean = new AccManageBean(id, typename,beizhu, money, year, month, day);
            list.add(accManageBean);
        }
        return list;
    }

    /*
        向记账表当中插入一条元素
     */
    public static void insertItemToAccounttb(AccountBean bean){
        ContentValues values = new ContentValues();
        values.put("typename",bean.getTypename());
        values.put("sImageId",bean.getsImageId());
        values.put("beizhu",bean.getBeizhu());
        values.put("money",bean.getMoney());
        values.put("time",bean.getTime());
        values.put("account",bean.getAccount());
        values.put("year",bean.getYear());
        values.put("month",bean.getMonth());
        values.put("day",bean.getDay());
        values.put("kind",bean.getKind());
        db.insert("accounttb",null,values);
        Log.i("animee","insetItemToAccounttb:ok!!!");
    }

    /*
        向帐户行当中插入一条元素
     */
    public static void insertItemToAccManagetb(AccManageBean bean){
        ContentValues values = new ContentValues();
        values.put("typename",bean.getTypename());
        values.put("beizhu",bean.getBeizhu());
        values.put("money",bean.getMoney());
        values.put("year",bean.getYear());
        values.put("month",bean.getMonth());
        values.put("day",bean.getDay());
        db.insert("accmanagetb",null,values);
        Log.i("animee","insetItemToAccManagetb:ok!!!");
    }

    // 获取记账表当中某一天的所有的支出和收入情况
    @SuppressLint("Range")
    public static List<AccountBean> getAccountListOneDayFromAccounttb(int year, int month, int day){
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from accounttb where year=? and month=? and day=? order by id desc";
        Cursor cursor = db.rawQuery(sql,new String[]{year+"",month+"",day+""});
        //遍历符合要求的每一行数据
        while (cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            String account = cursor.getString(cursor.getColumnIndex("account"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));

            AccountBean bean = new AccountBean(id, sImageId, kind, typename, beizhu, time, account, money, year, month, day);
            list.add(bean);
        }
        return list;
    }

    // 获取记账表当中某一月的所有的支出和收入情况
    @SuppressLint("Range")
    public static List<AccountBean> getAccountListOneMonthFromAccounttb(int year, int month){
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from accounttb where year=? and month=? order by day desc";
        Cursor cursor = db.rawQuery(sql,new String[]{year+"",month+""});
        //遍历符合要求的每一行数据
        while (cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            String account = cursor.getString(cursor.getColumnIndex("account"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean bean = new AccountBean(id, sImageId, kind, typename, beizhu, time, account, money, year, month, day);
            list.add(bean);
        }
        return list;
    }

    // 获取记账表当中某一年的所有的支出和收入情况
    @SuppressLint("Range")
    public static List<AccountBean> getAccountListOneYearFromAccounttb(int year){
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from accounttb where year=? order by month and day desc";
        Cursor cursor = db.rawQuery(sql,new String[]{year+""});
        //遍历符合要求的每一行数据
        while (cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            String account = cursor.getString(cursor.getColumnIndex("account"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int month = cursor.getInt(cursor.getColumnIndex("month"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean bean = new AccountBean(id, sImageId, kind, typename, beizhu, time, account, money, year, month, day);
            list.add(bean);
        }
        return list;
    }

    // 获取记账表当中某一月的所有的支出或收入情况
    @SuppressLint("Range")
    public static List<AccountBean> getAccountListOneMonthFromAccounttbByKind(int year, int month, int kind){
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from accounttb where year=? and month=? and kind=? order by day desc";
        Cursor cursor = db.rawQuery(sql,new String[]{year+"",month+"", kind+""});
        //遍历符合要求的每一行数据
        while (cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            String account = cursor.getString(cursor.getColumnIndex("account"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean bean = new AccountBean(id, sImageId, kind, typename, beizhu, time, account, money, year, month, day);
            list.add(bean);
        }
        return list;
    }

    // 获取记账表当中某一月的所有的支出或收入情况
    @SuppressLint("Range")
    public static List<AccountBean> getAccountListOneYearFromAccounttbByKind(int year, int kind){
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from accounttb where year=? and kind=? order by month and day desc";
        Cursor cursor = db.rawQuery(sql,new String[]{year+"", kind+""});
        //遍历符合要求的每一行数据
        while (cursor.moveToNext()){
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            String account = cursor.getString(cursor.getColumnIndex("account"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int month = cursor.getInt(cursor.getColumnIndex("month"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean bean = new AccountBean(id, sImageId, kind, typename, beizhu, time, account, money, year, month, day);
            list.add(bean);
        }
        return list;
    }

    @SuppressLint("Range")
    // 获取某一天的支出或收入的总金额
    public static float getSumMoneyOneDay(int year, int month, int day, int kind){
        float total = 0;
        String sql = "select sum(money) from accounttb where year=? and month=? and day=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", day + "", kind + ""});
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }

        return total;
    }

    @SuppressLint("Range")
    // 获取某一月的支出或收入的总金额
    public static float getSumMoneyOneMonth(int year, int month, int kind){
        float total = 0;
        String sql = "select sum(money) from accounttb where year=? and month=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }

        return total;
    }

    // 获取所有资产和花销的总和
    @SuppressLint("Range")
    public static float getSumMoneyAll(){
        float total = 0;
        String sql = "select sum(money) from accmanagetb";
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor.moveToFirst()){
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total += money;
        }
        return total;
    }

    @SuppressLint("Range")
    // 获取某一年的支出或收入的总金额
    public static float getSumMoneyOneYear(int year, int kind){
        float total = 0;
        String sql = "select sum(money) from accounttb where year=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", kind + ""});
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }

        return total;
    }

    //获取某一月所有存在记录的天数
    @SuppressLint("Range")
    public static List<Integer> selectAllDifferentDaysFromAccounttbByMonth(int year, int month){
        List<Integer> days = new ArrayList<>();
        String sql = "select distinct day from accounttb where year=? and month=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year+"",month+""});
        while (cursor.moveToNext()){
            days.add(cursor.getInt(cursor.getColumnIndex("day")));
        }
        return days;
    }

    //获取某一年所有存在记录的月份
    @SuppressLint("Range")
    public static List<Integer> selectAllDifferentMonthsFromAccounttbByYear(int year){
        List<Integer> months = new ArrayList<>();
        String sql = "select distinct month from accounttb where year=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year+""});
        while (cursor.moveToNext()){
            months.add(cursor.getInt(cursor.getColumnIndex("month")));
        }
        return months;
    }

    // 根据传入的id，删除accounttb当中的一条数据
    public static int deleteItemFromAccounttbById(final int click_id){
        int i = db.delete("accounttb", "id=?", new String[]{click_id + ""});
        return i;
    }

    // 根据传入的id，删除accounttb当中的一条数据
    public static int deleteItemFromAccmanagetbById(final int click_id){
        int i = db.delete("accmanagetb", "id=?", new String[]{click_id + ""});
        return i;
    }

    // 修改accounttb当中的一条信息
    @SuppressLint("LongLogTag")
    public static void alterItemFromAccounttbById(int id, AccountBean bean){
        ContentValues values = new ContentValues();
        values.put("typename",bean.getTypename());
        values.put("sImageId",bean.getsImageId());
        values.put("beizhu",bean.getBeizhu());
        values.put("money",bean.getMoney());
        values.put("time",bean.getTime());
        values.put("year",bean.getYear());
        values.put("month",bean.getMonth());
        values.put("day",bean.getDay());
        values.put("kind",bean.getKind());
        db.update("accounttb",values,"id=?",new String[]{id+""});
        Log.i("alterItemFromAccounttbById","ok!!!");
    }

    // 修改accounttb当中的一条信息
    @SuppressLint("LongLogTag")
    public static void alterItemFromAccmanagetbById(int id, AccManageBean bean){
        ContentValues values = new ContentValues();
        values.put("typename",bean.getTypename());
        values.put("beizhu",bean.getBeizhu());
        values.put("money",bean.getMoney());
        values.put("year",bean.getYear());
        values.put("month",bean.getMonth());
        values.put("day",bean.getDay());
        db.update("accmanagetb",values,"id=?",new String[]{id+""});
        Log.i("alterItemFromAccmanagetbById","ok!!!");
    }


}
